/*
 * This class is a utility to read the tables and their columns from the skyserver published schema
 * This scrapes the information from the Skserver UI and loads into the
 tables targettables & targetcolumns in our system. Only run one time.
 */
package com.querie.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.ArrayList;

/**
 *
 * @author a-skoshy
 */
public class DatabaseSchemaLoader implements Runnable{

    com.querie.beans.SchemaBrowser browser;
    Connection con = null;
    String startPattern;
    String endPattern;

    public DatabaseSchemaLoader(String startPattern, String endPattern) throws SQLException {
        browser = new com.querie.beans.SchemaBrowser();
        browser.setSTxtActivity("");
        browser.setSTxtComponentChoice("");
        browser.setSTxtSchemaBrowserChoice("VViews");
        con = DBMSConnection.getConnection();

        this.startPattern = startPattern;
        this.endPattern = endPattern;
    }

    public void run() {
        try {
            //cleanTables();
            browser.setSTxtSchemaBrowserChoice("UTables");
            insertTables("T", "C");
            browser.setSTxtSchemaBrowserChoice("VViews");
            insertTables("V", "CV");
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            try {
                DBMSConnection.safeClose(null, null, con);
            } catch (Exception e){
                e.printStackTrace();
            }
        }
    }

    public void insertTables(String tabIdentifier, String colIdentifier) throws Exception {
        String strSQL = "";
        PreparedStatement tblStmt = null;
        PreparedStatement colStmt = null;
        String tableid = null;
        String details = null;
        String table = null;
        String column = null;

        ArrayList<String> columns = null;

        //Remove all the data first
        //cleanTables();

        ArrayList ar = browser.getComponentList();
        strSQL = "INSERT INTO TARGETTABLES VALUES (?, ?)";
        System.out.println("Insert string:" + strSQL);
        tblStmt = con.prepareStatement(strSQL);

        strSQL = "INSERT INTO TARGETCOLUMNS VALUES (?, ?, ?)";
        System.out.println("Insert column string:" + strSQL);
        colStmt = con.prepareStatement(strSQL);
        
        for (int i = 0; i < ar.size(); i++) {
            table = (String) ar.get(i);
            System.out.println(table);
            browser.setSTxtComponentChoice(table);
            browser.setSTxtActivity("1");
            tableid = tabIdentifier + i;
            tblStmt.setString(1, tableid); //tableid
            tblStmt.setString(2, table.toUpperCase());//tablename
            tblStmt.executeUpdate();

            details = browser.getComponentDetails();
            System.out.println("details" + details);
            columns = getGroups(details, startPattern, endPattern);
            for (int j = 0; j < columns.size(); j++) {
                column = (String) columns.get(j);
                System.out.println("columnName:" + column);
                colStmt.setString(1, colIdentifier+ i + "_" +j); //columnid
                colStmt.setString(2, tableid);//tableid
                colStmt.setString(3, column.toUpperCase());//columnname
                colStmt.executeUpdate();
            }
        }

        DBMSConnection.safeClose(null, colStmt, null);
        DBMSConnection.safeClose(null, tblStmt, null);
    }

    public void cleanTables() throws SQLException {
        ResultSet rs0 = null;
        Statement stmt = null;


        try {
            String strSQL1 = "DELETE FROM TARGETTABLES";
            System.out.println("delete string:" + strSQL1);
            stmt = con.createStatement();
            stmt.executeUpdate(strSQL1);

            strSQL1 = "DELETE FROM TARGETCOLUMNS";
            System.out.println("delete string:" + strSQL1);
            stmt.executeUpdate(strSQL1);

        } catch (SQLException s) {
            s.printStackTrace();
            throw s;

        }

        DBMSConnection.safeClose(null, stmt, null);
    }

    private ArrayList<String> getGroups(String message, String startPattern, String endPattern) {
        Pattern startPat = null;
        Matcher startMat = null;
        Pattern endPat = null;
        Matcher endMat = null;
        String startGrp = null;
        String endGrp = null;


        int cnt = 0; //Cnt is for finding the first occurence and using in the start pos & endPos
        String column;
        ArrayList<String> columns = new ArrayList<String>();



        startPat = Pattern.compile(startPattern);
        startMat = startPat.matcher(message);
        endPat = Pattern.compile(endPattern);
        endMat = endPat.matcher(message);

        //System.out.println(" message:" + message );

        cnt = 0;
        while (startMat.find()) {
            //if(cnt == 0) continue;
            startGrp = startMat.group();
            //System.out.println(" startGrp:" + startGrp + " start:" + startMat.start() +" end:"  + startMat.end());

            if (endMat.find(startMat.end())) {
                endGrp = endMat.group();
                //System.out.println(" endGrp:" + endGrp + " start:" + endMat.start() +" end:"  + endMat.end());
                column = message.substring(startMat.end(), endMat.start()).trim();
                //System.out.println("column:" + column + " startGrp:" + startGrp + " endGrp"+endGrp);
                columns.add(column);
            }

            
            
            cnt++;
        }


        return columns;



    }

    public static void main (String ARGS[]) throws SQLException{
        //DatabaseSchemaLoader loader = new DatabaseSchemaLoader();
        String details = "<h1><font size=-1>TABLE</font>&nbsp;&nbsp;TilingInfo</h1><table width='720'><tr><td class='t'> Contain info on what happened to a TargetObj in a run of tiling software. </td></tr></table><p><table width='720'><tr><td class='d'> </td></tr></table><p><TABLE border=0 bgcolor=#888888 width=720 cellspacing=3 cellpadding=3><tr><td class='h'>name</td><td class='h'>type</td><td class='h'>length</td><td class='h'>unit</td><td class='h'>ucd</td><td class='h'>description</td></tr><tr><td class='v'>tileRun</td><td class='v'>smallint</td><td class='v'>2</td><td class='v'>&nbsp;</td><td class='v'>ID_VERSION</td><td class='v'>run of tiling software</td></tr><tr><td class='v'>tid</td><td class='v'>int</td><td class='v'>4</td><td class='v'>&nbsp;</td><td class='v'>ID_CATALOG</td><td class='v'>unique ID of object within tiling run</td></tr><tr><td class='v'>tile</td><td class='v'>smallint</td><td class='v'>2</td><td class='v'>&nbsp;</td><td class='v'>ID_PLATE</td><td class='v'>Tile the object was assigned (-1 if not assigned to a tile)</td></tr><tr><td class='v'>tiPriority<a href='enum.asp?n=???'><img src='images/info.gif' border=0 alt='Link to ???'></a></td><td class='v'>int</td><td class='v'>4</td><td class='v'>&nbsp;</td><td class='v'>CODE_MISC</td><td class='v'>collision priority given during tiling</td></tr><tr><td class='v'>tiMask<a href='enum.asp?n=TiMask'><img src='images/info.gif' border=0 alt='Link to TiMask'></a></td><td class='v'>int</td><td class='v'>4</td><td class='v'>&nbsp;</td><td class='v'>CODE_MISC</td><td class='v'>mask bits to determine what happened during tiling</td></tr><tr><td class='v'>collisionGroupID</td><td class='v'>int</td><td class='v'>4</td><td class='v'>&nbsp;</td><td class='v'>ID_CATALOG</td><td class='v'>unique ID of collisionGroup in this tiling run</td></tr><tr><td class='v'>targetID</td><td class='v'>bigint</td><td class='v'>8</td><td class='v'>&nbsp;</td><td class='v'>ID_CATALOG</td><td class='v'>ID of Target</td></tr><tr><td class='v'>loadVersion</td><td class='v'>int</td><td class='v'>4</td><td class='v'>&nbsp;</td><td class='v'>ID_VERSION</td><td class='v'>Load Version</td></tr></TABLE>";
        //loader.getGroups(details, "<tr><td class='v'>", "</td><td class='v'>");
    }
}
